{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Window functions\n",
    "\n",
    "General Elections were held in the UK in 2015 and 2017. Every citizen votes in a constituency. The candidate who gains the most votes becomes MP for that constituency.\n",
    "\n",
    "All these results are recorded in a table ge\n",
    "\n",
    "yr\t| firstName\t| lastName\t| constituency\t| party\t| votes\n",
    "---:|-----------|-----------|---------------|-------|------:\n",
    "2015\t| Ian\t| Murray\t| S14000024\t| Labour\t| 19293\n",
    "2015\t| Neil\t| Hay\t| S14000024\t| Scottish National Party\t| 16656\n",
    "2015\t| Miles\t| Briggs\t| S14000024\t| Conservative | 8626\n",
    "2015\t| Phyl\t| Meyer\t| S14000024\t| Green\t| 2090\n",
    "2015\t| Pramod\t| Subbaraman\t| S14000024\t| Liberal Democrat\t| 1823\n",
    "2015\t| Paul\t| Marshall\t| S14000024\t| UK Independence Party\t | 601\n",
    "2015\t| Colin\t| Fox\t| S14000024\t| Scottish Socialist Party\t| 197\n",
    "2017\t| Ian\t| MURRAY\t| S14000024\t| Labour\t| 26269\n",
    "2017\t| Jim\t| EADIE\t| S14000024\t| SNP\t| 10755\n",
    "2017\t| Stephanie Jane Harley\t| SMITH\t| S14000024\t| Conservative\t| 9428\n",
    "2017\t| Alan Christopher\t| BEAL\t| S14000024\t| Liberal Democrats\t| 1388\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ····\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@localhost/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Warming up\n",
    "\n",
    "Show the **lastName, party** and **votes** for the **constituency** 'S14000024' in 2017."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "ge = pd.read_sql_table('ge', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>lastname</th>\n",
       "      <th>party</th>\n",
       "      <th>votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3971</th>\n",
       "      <td>BEAL</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>1388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5204</th>\n",
       "      <td>MURRAY</td>\n",
       "      <td>Labour</td>\n",
       "      <td>26269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5427</th>\n",
       "      <td>EADIE</td>\n",
       "      <td>SNP</td>\n",
       "      <td>10755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6738</th>\n",
       "      <td>SMITH</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>9428</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     lastname              party  votes\n",
       "3971     BEAL  Liberal Democrats   1388\n",
       "5204   MURRAY             Labour  26269\n",
       "5427    EADIE                SNP  10755\n",
       "6738    SMITH       Conservative   9428"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ge.loc[(ge['constituency']=='S14000024') & (ge['yr']==2017), \n",
    "       ['lastname', 'party', 'votes']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Who won?\n",
    "\n",
    "You can use the RANK function to see the order of the candidates. If you RANK using (ORDER BY votes DESC) then the candidate with the most votes has rank 1.\n",
    "\n",
    "**Show the party and RANK for constituency S14000024 in 2017. List the output by party**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>party</th>\n",
       "      <th>votes</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6738</th>\n",
       "      <td>Conservative</td>\n",
       "      <td>9428</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5204</th>\n",
       "      <td>Labour</td>\n",
       "      <td>26269</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3971</th>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>1388</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5427</th>\n",
       "      <td>SNP</td>\n",
       "      <td>10755</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  party  votes  rank\n",
       "6738       Conservative   9428   3.0\n",
       "5204             Labour  26269   1.0\n",
       "3971  Liberal Democrats   1388   4.0\n",
       "5427                SNP  10755   2.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = ge.loc[(ge['constituency']=='S14000024') & (ge['yr']==2017),\n",
    "       ['party', 'votes']]\n",
    "(a.assign(rank=a['votes'].rank(ascending=False))\n",
    "  .sort_values('party'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. PARTITION BY\n",
    "\n",
    "The 2015 election is a different PARTITION to the 2017 election. We only care about the order of votes for each year.\n",
    "\n",
    "**Use PARTITION to show the ranking of each party in S14000021 in each year. Include yr, party, votes and ranking (the party with the most votes is 1).**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>party</th>\n",
       "      <th>votes</th>\n",
       "      <th>posn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>914</th>\n",
       "      <td>2015</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>12465</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6199</th>\n",
       "      <td>2017</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>21496</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9090</th>\n",
       "      <td>2019</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>19451</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1765</th>\n",
       "      <td>2015</td>\n",
       "      <td>Labour</td>\n",
       "      <td>19295</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4311</th>\n",
       "      <td>2017</td>\n",
       "      <td>Labour</td>\n",
       "      <td>14346</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7541</th>\n",
       "      <td>2019</td>\n",
       "      <td>Labour</td>\n",
       "      <td>6855</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1349</th>\n",
       "      <td>2015</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>1069</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3958</th>\n",
       "      <td>2017</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>1112</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7273</th>\n",
       "      <td>2019</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>4174</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2144</th>\n",
       "      <td>2015</td>\n",
       "      <td>SNP</td>\n",
       "      <td>23013</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8566</th>\n",
       "      <td>2019</td>\n",
       "      <td>SNP</td>\n",
       "      <td>24877</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3192</th>\n",
       "      <td>2015</td>\n",
       "      <td>UKIP</td>\n",
       "      <td>888</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        yr              party  votes  posn\n",
       "914   2015       Conservative  12465   3.0\n",
       "6199  2017       Conservative  21496   1.0\n",
       "9090  2019       Conservative  19451   2.0\n",
       "1765  2015             Labour  19295   2.0\n",
       "4311  2017             Labour  14346   2.0\n",
       "7541  2019             Labour   6855   3.0\n",
       "1349  2015  Liberal Democrats   1069   4.0\n",
       "3958  2017  Liberal Democrats   1112   3.0\n",
       "7273  2019  Liberal Democrats   4174   4.0\n",
       "2144  2015                SNP  23013   1.0\n",
       "8566  2019                SNP  24877   1.0\n",
       "3192  2015               UKIP    888   5.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = ge[ge['constituency']=='S14000021'].copy()\n",
    "a['posn'] = (a.groupby('yr')['votes']\n",
    "              .rank(ascending=False))\n",
    "a[['yr', 'party', 'votes', 'posn']].sort_values(['party', 'yr'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Edinburgh Constituency\n",
    "\n",
    "Edinburgh constituencies are numbered S14000021 to S14000026.\n",
    "\n",
    "**Use PARTITION BY constituency to show the ranking of each party in Edinburgh in 2017. Order your results so the winners are shown first, then ordered by constituency.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>constituency</th>\n",
       "      <th>party</th>\n",
       "      <th>votes</th>\n",
       "      <th>posn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6199</th>\n",
       "      <td>S14000021</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>21496</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6955</th>\n",
       "      <td>S14000022</td>\n",
       "      <td>SNP</td>\n",
       "      <td>18509</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4779</th>\n",
       "      <td>S14000023</td>\n",
       "      <td>SNP</td>\n",
       "      <td>19243</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5204</th>\n",
       "      <td>S14000024</td>\n",
       "      <td>Labour</td>\n",
       "      <td>26269</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5443</th>\n",
       "      <td>S14000025</td>\n",
       "      <td>SNP</td>\n",
       "      <td>17575</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4491</th>\n",
       "      <td>S14000026</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>18108</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4311</th>\n",
       "      <td>S14000021</td>\n",
       "      <td>Labour</td>\n",
       "      <td>14346</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6184</th>\n",
       "      <td>S14000022</td>\n",
       "      <td>Labour</td>\n",
       "      <td>15084</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5070</th>\n",
       "      <td>S14000023</td>\n",
       "      <td>Labour</td>\n",
       "      <td>17618</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5427</th>\n",
       "      <td>S14000024</td>\n",
       "      <td>SNP</td>\n",
       "      <td>10755</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6004</th>\n",
       "      <td>S14000025</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>16478</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6957</th>\n",
       "      <td>S14000026</td>\n",
       "      <td>SNP</td>\n",
       "      <td>15120</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3958</th>\n",
       "      <td>S14000021</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>1112</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6977</th>\n",
       "      <td>S14000022</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>1849</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5190</th>\n",
       "      <td>S14000023</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>15385</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6738</th>\n",
       "      <td>S14000024</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>9428</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4970</th>\n",
       "      <td>S14000025</td>\n",
       "      <td>Labour</td>\n",
       "      <td>13213</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6612</th>\n",
       "      <td>S14000026</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>11559</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5828</th>\n",
       "      <td>S14000023</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>2579</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3971</th>\n",
       "      <td>S14000024</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>1388</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3960</th>\n",
       "      <td>S14000025</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>2124</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5731</th>\n",
       "      <td>S14000026</td>\n",
       "      <td>Labour</td>\n",
       "      <td>7876</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5686</th>\n",
       "      <td>S14000023</td>\n",
       "      <td>Green</td>\n",
       "      <td>1727</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5809</th>\n",
       "      <td>S14000026</td>\n",
       "      <td>SIR</td>\n",
       "      <td>132</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     constituency              party  votes  posn\n",
       "6199    S14000021       Conservative  21496   1.0\n",
       "6955    S14000022                SNP  18509   1.0\n",
       "4779    S14000023                SNP  19243   1.0\n",
       "5204    S14000024             Labour  26269   1.0\n",
       "5443    S14000025                SNP  17575   1.0\n",
       "4491    S14000026  Liberal Democrats  18108   1.0\n",
       "4311    S14000021             Labour  14346   2.0\n",
       "6184    S14000022             Labour  15084   2.0\n",
       "5070    S14000023             Labour  17618   2.0\n",
       "5427    S14000024                SNP  10755   2.0\n",
       "6004    S14000025       Conservative  16478   2.0\n",
       "6957    S14000026                SNP  15120   2.0\n",
       "3958    S14000021  Liberal Democrats   1112   3.0\n",
       "6977    S14000022  Liberal Democrats   1849   3.0\n",
       "5190    S14000023       Conservative  15385   3.0\n",
       "6738    S14000024       Conservative   9428   3.0\n",
       "4970    S14000025             Labour  13213   3.0\n",
       "6612    S14000026       Conservative  11559   3.0\n",
       "5828    S14000023  Liberal Democrats   2579   4.0\n",
       "3971    S14000024  Liberal Democrats   1388   4.0\n",
       "3960    S14000025  Liberal Democrats   2124   4.0\n",
       "5731    S14000026             Labour   7876   4.0\n",
       "5686    S14000023              Green   1727   5.0\n",
       "5809    S14000026                SIR    132   5.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = ge[(ge['constituency'].between('S14000021', 'S14000026')) &\n",
    "       (ge['yr']==2017)].copy()\n",
    "a['posn'] = (a.groupby('constituency')['votes']\n",
    "             .rank(ascending=False))\n",
    "(a[['constituency', 'party', 'votes', 'posn']]\n",
    "   .sort_values(['posn', 'constituency']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Winners Only\n",
    "\n",
    "You can use [SELECT within SELECT](https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial) to pick out only the winners in Edinburgh.\n",
    "\n",
    "**Show the parties that won for each Edinburgh constituency in 2017.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>constituency</th>\n",
       "      <th>party</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6199</th>\n",
       "      <td>S14000021</td>\n",
       "      <td>Conservative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6955</th>\n",
       "      <td>S14000022</td>\n",
       "      <td>SNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4779</th>\n",
       "      <td>S14000023</td>\n",
       "      <td>SNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5204</th>\n",
       "      <td>S14000024</td>\n",
       "      <td>Labour</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5443</th>\n",
       "      <td>S14000025</td>\n",
       "      <td>SNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4491</th>\n",
       "      <td>S14000026</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     constituency              party\n",
       "6199    S14000021       Conservative\n",
       "6955    S14000022                SNP\n",
       "4779    S14000023                SNP\n",
       "5204    S14000024             Labour\n",
       "5443    S14000025                SNP\n",
       "4491    S14000026  Liberal Democrats"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = ge[(ge['constituency'].between('S14000021', 'S14000026')) & (ge['yr']==2017)].copy()\n",
    "a['posn'] = a.groupby('constituency')['votes'].rank(ascending=False)\n",
    "a.loc[a['posn']==1, ['constituency', 'party']].sort_values('constituency')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Scottish seats\n",
    "\n",
    "You can use **COUNT** and **GROUP BY** to see how each party did in Scotland. Scottish constituencies start with 'S'\n",
    "\n",
    "**Show how many seats for each party in Scotland in 2017.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>party</th>\n",
       "      <th>n</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Conservative</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Labour</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SNP</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               party   n\n",
       "0       Conservative  12\n",
       "1             Labour   9\n",
       "2  Liberal Democrats   4\n",
       "3                SNP  34"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = ge[(ge['constituency'].str.startswith('S')) &\n",
    "       (ge['yr']==2017)].copy()\n",
    "a['posn'] = (a.groupby('constituency')['votes']\n",
    "              .rank(ascending=False))\n",
    "(a[a['posn']==1].groupby('party')['yr']\n",
    "     .count()\n",
    "     .reset_index()\n",
    "     .rename(columns={'yr': 'n'}))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
